﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;

namespace XunChi.DBHelper
{
    internal class SQLiteHelper
    {

        /// <summary>
        /// 准备执行命令
        /// </summary>
        /// <param name="cmd">SqliteCommand对象</param>
        /// <param name="conn">SqliteConnection对象</param>
        /// <param name="trans">SqliteTransaction对象</param>
        /// <param name="cmdType">Cmd类型，例如：存储过程或文本</param>
        /// <param name="cmdText">命令文本，例如：Select * from Products</param>
        /// <param name="cmdParms">要在命令中使用的SqliteParameters</param>
        private static void PrepareCommand(SqliteCommand cmd, SqliteConnection conn, SqliteTransaction trans, CommandType cmdType, string cmdText, SqliteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) //这里似乎可以改进，因为如果连接已经打开就不必要重新打开数据库连接了。
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqliteParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// 根据参数对连接字符串中指定的数据库执行SqlCommand（不返回结果集）
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqliteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">SqliteConnection的有效连接字符串</param>
        /// <param name="commandType">CommandType（存储过程、文本等）</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">用于执行命令的SqliteParameter数组</param>
        /// <returns>一个int，表示受命令影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqliteParameter[] commandParameters)
        {

            SqliteCommand cmd = new SqliteCommand();

            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int affectedRows = cmd.ExecuteNonQuery();//对连接执行 Transact-SQL 语句并返回受影响的行数。
                cmd.Parameters.Clear();//清空Transact-SQL 语句或存储过程的参数。
                return affectedRows;
            }
        }
    }
}
